const XLSX = require('xlsx');
const path = require('path');
const fs = require('fs');
const db = require('../config/db');
const Bill = require('../models/Bill');

class ImportController {
  // Import bills from Excel/CSV file
  static async importBills(req, res) {
    try {
      // Check if file was uploaded
      if (!req.files || !req.files.file) {
        return res.status(400).json({ error: 'No file uploaded' });
      }

      const file = req.files.file;
      const user_id = req.userId; // Set by auth middleware
      
      // Validate file type
      const allowedTypes = [
        'application/vnd.ms-excel',
        'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet',
        'text/csv',
        'application/csv'
      ];
      
      if (!allowedTypes.includes(file.mimetype)) {
        return res.status(400).json({ error: 'Invalid file type. Please upload Excel or CSV file.' });
      }
      
      // Create temporary file path
      const tempPath = path.join(__dirname, '..', 'temp', `${Date.now()}-${file.name}`);
      
      // Ensure temp directory exists
      const tempDir = path.dirname(tempPath);
      if (!fs.existsSync(tempDir)) {
        fs.mkdirSync(tempDir, { recursive: true });
      }
      
      // Move uploaded file to temp location
      await file.mv(tempPath);
      
      // Parse the file based on extension
      let jsonData;
      if (file.name.endsWith('.csv')) {
        jsonData = ImportController.parseCSV(tempPath);
      } else {
        jsonData = ImportController.parseExcel(tempPath);
      }
      
      // Clean up temp file
      fs.unlinkSync(tempPath);
      
      // Validate and import data
      const result = await ImportController.importBillData(jsonData, user_id);
      
      res.json({
        message: 'Bills imported successfully',
        imported: result.imported,
        skipped: result.skipped,
        errors: result.errors
      });
    } catch (error) {
      console.error('Import bills error:', error);
      res.status(500).json({ error: 'Internal server error during import' });
    }
  }
  
  // Parse Excel file
  static parseExcel(filePath) {
    try {
      const workbook = XLSX.readFile(filePath);
      const firstSheetName = workbook.SheetNames[0];
      const worksheet = workbook.Sheets[firstSheetName];
      return XLSX.utils.sheet_to_json(worksheet);
    } catch (error) {
      throw new Error(`Error parsing Excel file: ${error.message}`);
    }
  }
  
  // Parse CSV file
  static parseCSV(filePath) {
    try {
      const workbook = XLSX.readFile(filePath, { type: 'string' });
      const firstSheetName = workbook.SheetNames[0];
      const worksheet = workbook.Sheets[firstSheetName];
      return XLSX.utils.sheet_to_json(worksheet);
    } catch (error) {
      throw new Error(`Error parsing CSV file: ${error.message}`);
    }
  }
  
  // Import bill data to database
  static async importBillData(data, user_id) {
    let imported = 0;
    let skipped = 0;
    const errors = [];
    
    // Get user's categories for validation
    const categoriesResult = await db.query(
      `SELECT id, name, type FROM categories WHERE user_id = 0 OR user_id = $1`,
      [user_id]
    );
    const categories = categoriesResult.rows;
    
    // Get user's accounts for validation
    const accountsResult = await db.query(
      `SELECT id, name FROM accounts WHERE user_id = $1`,
      [user_id]
    );
    const accounts = accountsResult.rows;
    
    // Process each row
    for (let i = 0; i < data.length; i++) {
      try {
        const row = data[i];
        
        // Map fields from the Excel/CSV file
        // This mapping may need to be adjusted based on the actual file structure
        const billData = ImportController.mapBillData(row, categories, accounts);
        
        // Validate required fields
        if (!billData.category_id || !billData.amount || !billData.type || !billData.bill_date) {
          errors.push(`Row ${i + 1}: Missing required fields`);
          skipped++;
          continue;
        }
        
        // Validate category
        const category = categories.find(c => 
          c.id == billData.category_id || 
          c.name.toLowerCase() === billData.category_name.toLowerCase()
        );
        
        if (!category) {
          errors.push(`Row ${i + 1}: Invalid category`);
          skipped++;
          continue;
        }
        
        // Use found category ID
        billData.category_id = category.id;
        
        // Validate account if provided
        if (billData.account_name) {
          const account = accounts.find(a => 
            a.id == billData.account_id || 
            a.name.toLowerCase() === billData.account_name.toLowerCase()
          );
          
          if (account) {
            billData.account_id = account.id;
          } else {
            billData.account_id = null; // Use default account
          }
        }
        
        // Create bill
        const result = await db.query(
          `INSERT INTO bills (user_id, category_id, title, amount, type, bill_date, description, payment_method, account_id) 
           VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9) RETURNING *`,
          [
            user_id,
            billData.category_id,
            billData.title,
            billData.amount,
            billData.type,
            billData.bill_date,
            billData.description,
            billData.payment_method,
            billData.account_id
          ]
        );
        
        if (result && result.rows && result.rows.length > 0) {
          imported++;
        } else {
          errors.push(`Row ${i + 1}: Failed to import bill`);
          skipped++;
        }
      } catch (error) {
        errors.push(`Row ${i + 1}: ${error.message}`);
        skipped++;
      }
    }
    
    return { imported, skipped, errors };
  }
  
  // Map bill data from Excel/CSV row
  static mapBillData(row, categories, accounts) {
    // This is a generic mapping that should be adjusted based on actual file structure
    // Common column names that might be used in finance apps
    const billData = {
      // Title mapping - try common column names
      title: row.title || row.Title || row['账单标题'] || row['标题'] || row['Description'] || row['描述'] || 'Imported Bill',
      
      // Amount mapping
      amount: parseFloat(row.amount || row.Amount || row['金额'] || row['Amount'] || 0),
      
      // Type mapping
      type: (row.type || row.Type || row['类型'] || row['Type'] || '').toLowerCase(),
      
      // Date mapping
      bill_date: row.date || row.Date || row['日期'] || row['Date'] || new Date().toISOString().split('T')[0],
      
      // Category mapping
      category_name: row.category || row.Category || row['分类'] || row['Category'] || '',
      
      // Account mapping
      account_name: row.account || row.Account || row['账户'] || row['Account'] || '',
      
      // Description mapping
      description: row.description || row.Description || row['备注'] || row['备注'] || '',
      
      // Payment method mapping
      payment_method: row.payment_method || row['支付方式'] || row['Payment Method'] || ''
    };
    
    // Determine type if not explicitly set
    if (!billData.type && billData.amount) {
      billData.type = billData.amount >= 0 ? 'income' : 'expense';
    }
    
    // Normalize amount to positive value for expense type
    if (billData.type === 'expense' && billData.amount > 0) {
      billData.amount = -billData.amount;
    }
    
    return billData;
  }
}

module.exports = ImportController;